#include "SQLAction.hpp"
#include <stdexcept>
#include <fmt/format.h>
#include <spdlog/spdlog.h>
#include <string>
#include <tuple>

#define HANDLE_SQL_WITH_EXCEPT(para) \
  try { para; DBwork.exec("COMMIT");} catch (const std::exception& e) \
  { DBwork.exec("ROLLBACK"); throw;}

SQLAction::SQLAction(std::string user,std::string passwd,std::string host,std::string database)
  : DBconn(fmt::format("postgresql://{}:{}@{}/{}",user,passwd,host,database))
  , DBwork(DBconn)
{
  DBconn.prepare("insert_student","INSERT INTO student_inf VALUES ($1,$2)");
  DBconn.prepare("delete_student","DELETE FROM student_inf WHERE stu_id=$1");
  DBconn.prepare("create_task","INSERT INTO task_inf (name) VALUES ($1) RETURNING task_id");
  DBconn.prepare("delete_task","DELETE FROM task_inf WHERE task_id=$1");
  DBconn.prepare("create_record","INSERT INTO record (stu_id,task_id) VALUES ($1,$2) RETURNING rec_id");
  DBconn.prepare("finish_record","UPDATE record SET record_finish='t' WHERE rec_id=$1");
  DBconn.prepare("delete_record","DELETE FROM record WHERE rec_id=$1");
  DBconn.prepare("handle_finish","UPDATE record SET smpl_status='t' WHERE rec_id=$1");
}

SQLAction::~SQLAction()
{
}

std::deque<DB_StudentInf> SQLAction::GetStudentInf()
{
  std::deque<DB_StudentInf> res={};
  for (auto [id, name] : DBwork.query<long, std::string>("SELECT * FROM student_inf"))
    res.emplace_back(id,name);
  return res;
}

std::string SQLAction::GetStudentInf(long stu_id)
{
  pqxx::result res = DBwork.exec(fmt::format("SELECT name FROM student_inf WHERE stu_id={}",stu_id));
  if(res.columns()==0)
    throw std::runtime_error("Can't get student_inf");
  else
    return *res[0][0].get<std::string>();
}

void SQLAction::CreateStudent(int id,std::string Name)
{
  HANDLE_SQL_WITH_EXCEPT(DBwork.exec_prepared("insert_student",id,Name))
}

void SQLAction::DeleteStudent(int id)
{
  HANDLE_SQL_WITH_EXCEPT(DBwork.exec_prepared("delete_student",id))
}

long SQLAction::CreateTask(std::string name)
{
  pqxx::result task_res;
  HANDLE_SQL_WITH_EXCEPT(task_res=DBwork.exec_prepared("create_task",name))
  if(std::size(task_res)!=1)
    throw std::runtime_error("Task insert failed");
  auto row=task_res[0];
  long taskID = row[0].as<long>();
  return taskID;
}

void SQLAction::DeleteTask(int id)
{
  HANDLE_SQL_WITH_EXCEPT(DBwork.exec_prepared("delete_task",id))
}

std::deque<DB_TaskInf> SQLAction::GetTaskInf()
{
  std::deque<DB_TaskInf> res;
  // auto stream=pqxx::stream_from::query(DBwork,"SELECT task_id,name,create_time FROM task_inf");
  // std::tuple<long,std::string,std::string> row;
  // while(stream>>row)
  //   res.emplace_back(std::get<0>(row),std::get<1>(row),std::get<2>(row));
  // return res;
  for(auto [id,name,time] : DBwork.query<long,std::string,std::string>("SELECT task_id,name,create_time FROM task_inf"))
    res.emplace_back(id,name,time);
  return res;
}

long SQLAction::CreateRecord(long stu_id,long task_id)
{
  pqxx::result execres;
  HANDLE_SQL_WITH_EXCEPT(execres=DBwork.exec_prepared("create_record",stu_id,task_id))
  if(std::size(execres)!=1)
    throw std::runtime_error("Task insert failed");
  auto row=execres[0];
  long taskID = row[0].as<long>();
  return taskID;
}

void SQLAction::RecordFinish(long id)
{
  HANDLE_SQL_WITH_EXCEPT(DBwork.exec_prepared("finish_record",id))
}

void SQLAction::DeleteRecord(long id)
{
  HANDLE_SQL_WITH_EXCEPT(DBwork.exec_prepared("delete_record",id));
}


std::deque<long> SQLAction::GetRecordIDList(long task_id)
{
  std::deque<long> res;
  for(auto [rec_id] : DBwork.query<long>(fmt::format("SELECT rec_id FROM record WHERE task_id={}", task_id)))
    res.push_back(rec_id);

  return res;
}

std::deque<long> SQLAction::GetHandleIDList()
{
  std::deque<long> res;
  for(auto [rec_id] : DBwork.query<long>("SELECT rec_id FROM record WHERE smpl_status='t' AND record_finish='t'"))
    res.push_back(rec_id);
  return res;
}

std::deque<long> SQLAction::GetUnhandleIDList()
{
  std::deque<long> res;
  for(auto [rec_id] : DBwork.query<long>("SELECT rec_id FROM record WHERE smpl_status='f' AND record_finish='t'"))
    res.push_back(rec_id);

  return res;
}

std::tuple<long,long,std::string,bool,bool> SQLAction::GetRecordInf(long id)
{
  pqxx::result res = DBwork.exec(fmt::format("SELECT stu_id,task_id,create_time,smpl_status,record_finish FROM record WHERE rec_id={}",id));
  if(res.columns()==0)
    throw std::runtime_error("Can't get student_inf");
  else
    return std::make_tuple(
      *res[0][0].get<long>(),
      *res[1][0].get<long>(),
      *res[2][0].get<std::string>(),
      *res[3][0].get<bool>(),
      *res[4][0].get<bool>()
    );

}

bool SQLAction::CheckStudentExists(long id)
{
  pqxx::result res = DBwork.exec(fmt::format("SELECT COUNT(stu_id) FROM student_inf WHERE stu_id={}",id));
  if(res.columns()==0)
    throw std::runtime_error("Can't get student_inf");
  else
    return *res[0][0].get<bool>();
}

void SQLAction::SetHandleFinish(long id)
{
  HANDLE_SQL_WITH_EXCEPT(DBwork.exec_prepared("handle_finish",id))
}


std::vector<long> SQLAction::GetHandleIDListWithTask(long task_id)
{
  std::vector<long> res;
  for(auto [rec_id] : DBwork.query<long>(fmt::format("SELECT rec_id FROM record WHERE task_id={} AND record_finish=true",task_id)))
    res.push_back(rec_id);
  return res;
}
